import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class DatabaseManager {
    // 数据库连接信息
    private static final String URL = "jdbc:mysql://localhost:3306/todo_management";
    private static final String USER = "root";
    private static final String PASSWORD = "xc052051"; // 你的MySQL密码

    // 获取数据库连接
    private static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void initDatabase() {
        // 先连接到 MySQL 的根实例以创建数据库
        String rootUrl = "jdbc:mysql://localhost:3306/?user=" + USER + "&password=" + PASSWORD;
        try (Connection rootConn = DriverManager.getConnection(rootUrl)) {
            // 尝试创建数据库
            String createDatabase = "CREATE DATABASE IF NOT EXISTS todo_management";
            try (Statement stmt = rootConn.createStatement()) {
                stmt.execute(createDatabase);
//                System.out.println("数据库已创建或已存在");
            }
        } catch (SQLException e) {
            System.out.println("创建数据库失败: " + e.getMessage());
            return;
        }

        // 连接到具体的数据库并创建表
        try (Connection conn = getConnection()) {
            // 创建用户表的 SQL
            String createUserTable = """
                        CREATE TABLE IF NOT EXISTS Users (
                            user_id INT PRIMARY KEY AUTO_INCREMENT,
                            username VARCHAR(100) UNIQUE NOT NULL,
                            password VARCHAR(100) NOT NULL
                        )
                    """;
            // 创建任务表的 SQL
            String createTaskTable = """
                        CREATE TABLE IF NOT EXISTS Tasks (
                            task_id INT PRIMARY KEY AUTO_INCREMENT,
                            user_id INT,
                            title VARCHAR(200) NOT NULL,
                            description TEXT,
                            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                            due_date DATETIME,
                            priority VARCHAR(20),
                            tags TEXT,
                            status BOOLEAN DEFAULT FALSE,
                            reminder_time DATETIME,
                            FOREIGN KEY (user_id) REFERENCES Users(user_id)
                        )
                    """;

            // 执行建表语句
            try (Statement stmt = conn.createStatement()) {
                stmt.execute(createUserTable);
                stmt.execute(createTaskTable);
//                System.out.println("数据库表已创建或已存在");
            }
        } catch (SQLException e) {
            System.out.println("创建数据库表失败: " + e.getMessage());
        }
    }

    // 保存新用户
    public static void saveUser(User user) {
        String sql = "INSERT INTO Users (username, password) VALUES (?, ?)";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setString(1, user.getUsername());  // 设置用户名
            pstmt.setString(2, user.getPassword());  // 设置密码
            pstmt.executeUpdate();  // 执行插入

        } catch (SQLException e) {
            System.out.println("保存用户失败: " + e.getMessage());
        }
    }

    // 加载所有用户
    public static List<User> loadUsers() {
        List<User> users = new ArrayList<>();
        String sql = "SELECT * FROM Users";

        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                // 为每个用户创建一个User对象
                User user = new User(
                        rs.getInt("user_id"),
                        rs.getString("username"),
                        rs.getString("password")
                );
                users.add(user);
            }

        } catch (SQLException e) {
            System.out.println("加载用户失败: " + e.getMessage());
        }
        return users;
    }

    // 保存新任务
    public static void saveTask(Task task) {
        String sql = """
                    INSERT INTO Tasks (user_id, title, description, due_date, 
                                     priority, tags, reminder_time)
                    VALUES (?, ?, ?, ?, ?, ?, ?)
                """;

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置任务的各个属性
            pstmt.setInt(1, task.getUserId());
            pstmt.setString(2, task.getTitle());
            pstmt.setString(3, task.getDescription());
            pstmt.setTimestamp(4, task.getDueDate() != null ?
                    Timestamp.valueOf(task.getDueDate()) : null);
            pstmt.setString(5, task.getPriority());
            pstmt.setString(6, String.join(",", task.getTags()));
            pstmt.setTimestamp(7, task.getReminderTime() != null ?
                    Timestamp.valueOf(task.getReminderTime()) : null);

            pstmt.executeUpdate();  // 执行插入

        } catch (SQLException e) {
            System.out.println("保存任务失败: " + e.getMessage());
        }
    }

    // 加载用户的所有任务
    public static List<Task> loadTasks(int userId) {
        List<Task> tasks = new ArrayList<>();
        String sql = "SELECT * FROM Tasks WHERE user_id = ? AND status = FALSE";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                // 创建任务对象并设置属性
                Task task = new Task(
                        rs.getInt("user_id"),
                        rs.getString("title"),
                        rs.getString("description"),
                        rs.getString("priority")
                );
                task.setTaskId(rs.getInt("task_id"));

                // 设置截止时间
                Timestamp dueDate = rs.getTimestamp("due_date");
                if (dueDate != null) {
                    task.setDueDate(dueDate.toLocalDateTime());
                }

                // 设置标签
                String tags = rs.getString("tags");
                if (tags != null && !tags.isEmpty()) {
                    task.getTags().addAll(Arrays.asList(tags.split(",")));
                }

                // 设置提醒时间
                Timestamp reminderTime = rs.getTimestamp("reminder_time");
                if (reminderTime != null) {
                    task.setReminderTime(reminderTime.toLocalDateTime());
                }

                tasks.add(task);
            }

        } catch (SQLException e) {
            System.out.println("加载任务失败: " + e.getMessage());
        }
        return tasks;
    }

    // 更新任务
    public static void updateTask(Task task) {
        String sql = """
                    UPDATE Tasks 
                    SET title = ?, 
                        description = ?, 
                        due_date = ?, 
                        priority = ?, 
                        tags = ?, 
                        reminder_time = ?, 
                        status = ?
                    WHERE task_id = ?
                """;

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置更新的值
            pstmt.setString(1, task.getTitle());
            pstmt.setString(2, task.getDescription());
            pstmt.setTimestamp(3, task.getDueDate() != null ?
                    Timestamp.valueOf(task.getDueDate()) : null);
            pstmt.setString(4, task.getPriority());
            pstmt.setString(5, String.join(",", task.getTags()));
            pstmt.setTimestamp(6, task.getReminderTime() != null ?
                    Timestamp.valueOf(task.getReminderTime()) : null);
            pstmt.setBoolean(7, task.getStatus());
            pstmt.setInt(8, task.getTaskId());

            pstmt.executeUpdate();  // 执行更新

        } catch (SQLException e) {
            System.out.println("更新任务失败: " + e.getMessage());
        }
    }

    // 删除任务
    public static void deleteTask(int taskId) {
        String sql = "DELETE FROM Tasks WHERE task_id = ?";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, taskId);
            pstmt.executeUpdate();  // 执行删除

        } catch (SQLException e) {
            System.out.println("删除任务失败: " + e.getMessage());
        }
    }

    // 加载已完成的任务
    public static List<Task> loadCompletedTasks(int userId) {
        List<Task> tasks = new ArrayList<>();
        String sql = "SELECT * FROM Tasks WHERE user_id = ? AND status = TRUE";

        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            pstmt.setInt(1, userId);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                // 创建任务对象并设置属性
                Task task = new Task(
                        rs.getInt("user_id"),
                        rs.getString("title"),
                        rs.getString("description"),
                        rs.getString("priority")
                );
                task.setTaskId(rs.getInt("task_id"));

                // 设置截止时间
                Timestamp dueDate = rs.getTimestamp("due_date");
                if (dueDate != null) {
                    task.setDueDate(dueDate.toLocalDateTime());
                }

                // 设置标签
                String tags = rs.getString("tags");
                if (tags != null && !tags.isEmpty()) {
                    task.getTags().addAll(Arrays.asList(tags.split(",")));
                }

                // 设置提醒时间
                Timestamp reminderTime = rs.getTimestamp("reminder_time");
                if (reminderTime != null) {
                    task.setReminderTime(reminderTime.toLocalDateTime());
                }

                task.setStatus(true);
                tasks.add(task);
            }

        } catch (SQLException e) {
            System.out.println("加载已完成任务失败: " + e.getMessage());
        }
        return tasks;
    }
} 